package io.nutz.nutzsite.common.utils.excel;

import java.io.OutputStream;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.nutz.dao.entity.Record;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.mvc.annotation.At;


@IocBean
@At("/poi")
public class PoiAct {

	//Excel表格头样式
	private static CellStyle headS(SXSSFWorkbook  wb,int fontp){
		CellStyle headerStyle =wb.createCellStyle();
 		//headerStyle.setFillForegroundColor(IndexedColors.BLACK.index);
 		//headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
 		headerStyle.setBorderBottom(BorderStyle.THIN);
 		headerStyle.setBottomBorderColor(IndexedColors.RED.index);
 		headerStyle.setBorderLeft(BorderStyle.THIN);
 		headerStyle.setBorderRight(BorderStyle.THIN);
 		headerStyle.setBorderTop(BorderStyle.THIN);
 		headerStyle.setAlignment(HorizontalAlignment.CENTER);
 		headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直居中
 		//headerStyle.setRotation(arg0);//旋转文本
 		headerStyle.setLocked(true);
 		Font font0=wb.createFont();
 		font0.setFontName("宋体"); //设置字体
 		font0.setFontHeightInPoints((short)fontp); //设置字号
 		font0.setColor(IndexedColors.BLACK.index);
 		font0.setBold(true);//加粗
 		headerStyle.setFont(font0);
 		return headerStyle;
	}
	//Excel表格样式
	private static CellStyle dataS(SXSSFWorkbook  wb,short colorind,boolean isbold){
		CellStyle dataStyle =wb.createCellStyle();
 		//dataStyle.setFillForegroundColor(IndexedColors.BLACK.index);// 给单元格着色
 		//dataStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);//设置单元格填充样式
		//dataStyle.setWrapText(true);//// 指定当单元格内容显示不下时自动换行
 		dataStyle.setBorderBottom(BorderStyle.THIN);// 设置边框
 		dataStyle.setBorderLeft(BorderStyle.THIN);
 		dataStyle.setBorderRight(BorderStyle.THIN);
 		dataStyle.setBorderTop(BorderStyle.THIN);
 		dataStyle.setAlignment(HorizontalAlignment.LEFT);// 设置单元格水平对齐的类型
 		dataStyle.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直居中
 		Font font1=wb.createFont();
 		font1.setBold(isbold);
 		font1.setColor(colorind);
 		dataStyle.setFont(font1);// 把字体应用到当前的样式
 		return dataStyle;
	}
	/*
	 * 利用POI重新封装一个通用方法：将查询结果保存到Excel文件
	 * fname文件名和标题
	 * sc查询结果
	 * fdnames字段名
	 * headstr列标题
	 */
	public static void Record2xls(HttpServletResponse response,
			String fname, List<Record> sc,
			String[] fdnames, String[] headstr){
		try{
    	//创建新的Excel工作薄  
        SXSSFWorkbook  wb=new SXSSFWorkbook (); //streaming 方式，仅适用于生成excel,需使用poi-ooxml-3.16.jar           
        //如果新建一个名为“sheet1”的工作表  
        Sheet sheet=wb.createSheet("sheet1");
        //sheet.createFreezePane(4, 0);//冻结前四列
      //创建单元格样式——标题
        CellStyle titleStyle=headS(wb,20);
        //创建单元格样式——表头
        CellStyle headerStyle=headS(wb,12);
 		//创建单元格样式——数据
        CellStyle dataSblack=dataS(wb,IndexedColors.BLACK.index,false);
        CellStyle dataSred=dataS(wb,IndexedColors.RED.index,true);
        CellStyle dataSblue=dataS(wb,IndexedColors.BLUE.index,true);
        CellStyle dataSgreen=dataS(wb,IndexedColors.GREEN.index,true);
 		//设置列宽，以字符数为宽度单位
        //sheet.setColumnWidth(0, 5560);//Set the width (in units of 1/256th of a character width)
        //sheet.setColumnWidth(1, 5560); 
        
        //列的数量
        int colnum=headstr.length;

        Row row=null;  
        Cell cell=null;
        int rindex=0;
        row=sheet.createRow(rindex);//第0行
    
        //////////标题栏///////////////////////////////////////////
        if(fname!=null){
	       	//表格的标题，先创建带边框的cell，然后合并，避免边框不全
	        for(int i=0;i<colnum;i++){
				cell = row.createCell(i);//第i列单元格
				cell.setCellType(CellType.STRING);
				cell.setCellStyle(titleStyle);
	        }
	        // Set the height in "twips" or  1/20th of a point. 20 缇等于 1 磅 
	        row.setHeight((short) (25 * 20));  
	       // 合并单元格:起始行号，终止行号，起始列号，终止列号
	       	sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, colnum-1));
	       	cell=sheet.getRow(0).getCell(0);
			cell.setCellValue(fname);
			rindex++;
        }
        
        //////////表头/////////////////////////////       	
       	row=sheet.createRow(rindex);
        for(int i=0;i<colnum;i++){
				cell = row.createCell(i);
				cell.setCellType(CellType.STRING);//单元格数据类型
				cell.setCellStyle(headerStyle);//单元格样式
				cell.setCellValue(headstr[i]);//单元格数值
				//cell.setCellFormula("");//公式
		        //cell.setCellComment();//批注
        }
        
        ////////表格数据/////////////////////////////////////////
        int rows=sc.size();
        rindex++;
        for(int i=0;i<rows;i++)
        {
        	Record s=sc.get(i);
        	row=sheet.createRow(rindex+i);
        	for(int j=0;j<colnum;j++){ 
	        	cell = row.createCell(j);
				cell.setCellType(CellType.STRING);
				cell.setCellStyle(dataSblack);
				cell.setCellValue(s.getString(fdnames[j]));//全部转换为字符串
        	}
        }
        
      ///////导出到excel文件//////////////////////       
        OutputStream fOut = response.getOutputStream();//取得输出流
        response.reset();//清空输出流
        response.setCharacterEncoding("UTF-8");//设置相应内容的编码格式
        fname = java.net.URLEncoder.encode(fname,"UTF-8");
        fname=new String(fname.getBytes("UTF-8"),"ISO-8859-1");//避免中文文件名乱码
        response.setHeader("Content-Disposition","attachment;filename="+fname+".xlsx");
        response.setContentType("application/msexcel");//定义输出类型//"APPLICATION/OCTET-STREAM"
         
        wb.write(fOut);//将数据写入Excel  
        fOut.flush();  
        fOut.close(); 
    }catch(Exception e){  
        e.printStackTrace();  
    }finally {  
          
    } 
	}

}